/***********************************************************************************************************************************
 * Name:	Server_Database-Mail.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Set up Database Mail.
 */
USE msdb;

EXECUTE master.sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1;
EXECUTE master.sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SQL Server Service';
EXECUTE msdb.dbo.sp_set_sqlagent_properties @email_profile=N'SQL Server Service';
EXECUTE msdb.dbo.sp_MSsetalertinfo @failsafeoperator=N'', @notificationmethod= 0;
BEGIN TRY EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp	@profile_name= N'SQL Server Service', @account_name= N'SQL Server Service'	END TRY BEGIN CATCH END CATCH;
BEGIN TRY EXECUTE msdb.dbo.sysmail_delete_profile_sp		@profile_name= N'SQL Server Service'										END TRY BEGIN CATCH END CATCH;
BEGIN TRY EXECUTE msdb.dbo.sysmail_delete_account_sp		@account_name= N'SQL Server Service'										END TRY BEGIN CATCH END CATCH;
BEGIN TRY EXECUTE msdb.dbo.sp_delete_operator						@name= N'(DB)A-Team'												END TRY BEGIN CATCH END CATCH;
IF NOT EXISTS (SELECT * FROM msdb.dbo.syscategories WHERE name = N'[DBAdmin]' AND category_class = 3)
  EXECUTE msdb.dbo.sp_add_category @class= N'OPERATOR', @type= N'NONE', @name= N'[DBAdmin]';
GO

DECLARE
	@dbateam_email	NVARCHAR( 32)= N'dbateam@sqlnerd.me',
	@mailserver		NVARCHAR( 32)= N'smtp.sqlnerd.me',
	@email_address	NVARCHAR(255)= @@SERVERNAME + N'@sqlnerd.me',
	@display_name	NVARCHAR(255)= @@SERVERNAME + N' SQL Server',
	@account_id		INT,
	@profile_id		INT;

EXECUTE msdb.dbo.sysmail_add_account_sp
	@account_name	= N'SQL Server Service',
	@description	= N'Send email using SQL Server Service credentials identifying as this server.',
	@display_name	= @display_name,
	@email_address	= @email_address,
	@replyto_address= @dbateam_email,
	@mailserver_name= @mailserver,
	@account_id		= @account_id OUTPUT;

EXECUTE msdb.dbo.sysmail_add_profile_sp
	@profile_name	= N'SQL Server Service',
	@description	= N'DBA Mail',
	@profile_id		= @profile_id OUTPUT;

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
	@profile_id		= @profile_id,
	@account_id		= @account_id,
	@sequence_number= 1;

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
	@principal_id	= 0,
	@profile_name	= N'SQL Server Service',
	@is_default		= 1;

EXECUTE msdb.dbo.sp_add_operator @name= N'(DB)A-Team', @enabled= 1, @email_address= @dbateam_email, @category_name= N'[DBAdmin]';
EXECUTE msdb.dbo.sp_MSsetalertinfo @failsafeoperator= N'(DB)A-Team', @notificationmethod= 1;
GO